/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */

package ar.lucas.controlgasto.utils;

import ar.lucas.controlgasto.conexion.MyConnection;

import com.mysql.jdbc.Connection;
import com.mysql.jdbc.Statement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.util.logging.Level;
import java.util.logging.Logger;
import javax.swing.JLabel;
import javax.swing.JTable;
import javax.swing.SwingConstants;
import javax.swing.table.DefaultTableCellRenderer;
import javax.swing.table.TableColumn;


/**
 *
 * @author lucmussi
 */
public class TableUtils {

    private static void setColumnWidth(TableColumn column, int width)
    {
        column.setPreferredWidth(width);
        column.setMaxWidth(width);
    }

    public static void fillTableHeaders(JTable tabla){
        Connection con = (Connection) MyConnection.getConnection();
        try {
            ResultSetMetaData metadatos = null;
            String query = "select * from gasto_general";
            ResultSet rs;
            Statement s = (Statement) con.createStatement();
            rs = s.executeQuery(query);
            metadatos = rs.getMetaData();
            String[] encabezado = new String[metadatos.getColumnCount()];
            for (int i = 1; i <= metadatos.getColumnCount(); i++) {
                encabezado[i - 1] = metadatos.getColumnName(i);
            }
            tabla.setModel(new javax.swing.table.DefaultTableModel(null, encabezado));
        } catch (SQLException ex) {
            Logger.getLogger(TableUtils.class.getName()).log(Level.SEVERE, null, ex);
        }
    }

    public static void buscarGastosMesActual(JTable tabla, int mes){
        Connection con = (Connection) MyConnection.getConnection();
        ListTableModel model = null;
        ResultSet rs;
        try {
            String query = "select * from gasto_general where MONTH(Fecha)="+mes;
            
            Statement s = (Statement) con.createStatement();
            rs = s.executeQuery(query);
            model = ListTableModel.createModelFromResultSet( rs );
            DefaultTableCellRenderer dtcr = new DefaultTableCellRenderer();
            dtcr.setHorizontalAlignment(SwingConstants.CENTER);
            tabla.setModel( model );
            setColumnWidth(tabla.getColumnModel().getColumn(0), 100);
            setColumnWidth(tabla.getColumnModel().getColumn(1), 250);
            setColumnWidth(tabla.getColumnModel().getColumn(2), 43);
            setColumnWidth(tabla.getColumnModel().getColumn(3), 72);
            ((DefaultTableCellRenderer)tabla.getTableHeader().getDefaultRenderer()).setHorizontalAlignment(JLabel.CENTER);
            for(int i=0;i<tabla.getColumnCount();i++){
                tabla.getColumnModel().getColumn(i).setCellRenderer(dtcr);
            }

        } catch (SQLException ex) {
            Logger.getLogger(TableUtils.class.getName()).log(Level.SEVERE, null, ex);
        }

    }

    public static void buscarAllGastos(JTable tabla){
        Connection con = (Connection) MyConnection.getConnection();
        ListTableModel model = null;
        ResultSet rs;
        try {
            String query = "select * from gasto_general";
            Statement s = (Statement) con.createStatement();
            rs = s.executeQuery(query);
            model = ListTableModel.createModelFromResultSet( rs );
            DefaultTableCellRenderer dtcr = new DefaultTableCellRenderer();
            dtcr.setHorizontalAlignment(SwingConstants.CENTER);
            tabla.setModel( model );
            setColumnWidth(tabla.getColumnModel().getColumn(0), 100);
            setColumnWidth(tabla.getColumnModel().getColumn(1), 250);
            setColumnWidth(tabla.getColumnModel().getColumn(2), 43);
            setColumnWidth(tabla.getColumnModel().getColumn(3), 72);
            
            tabla.getColumnModel().getColumn(1).setCellRenderer(dtcr);
            tabla.getColumnModel().getColumn(2).setCellRenderer(dtcr);
        } catch (SQLException ex) {
            Logger.getLogger(TableUtils.class.getName()).log(Level.SEVERE, null, ex);
        }

    }

    public static void buscarAllGastosConId(JTable tabla){
        Connection con = (Connection) MyConnection.getConnection();
        ListTableModel model = null;
        ResultSet rs;
        try {
            String query = "select * from gasto_general_id";
            Statement s = (Statement) con.createStatement();
            rs = s.executeQuery(query);
            model = ListTableModel.createModelFromResultSet( rs );
            DefaultTableCellRenderer dtcr = new DefaultTableCellRenderer();
            dtcr.setHorizontalAlignment(SwingConstants.CENTER);
            tabla.setModel( model );
            setColumnWidth(tabla.getColumnModel().getColumn(0), 25);
            setColumnWidth(tabla.getColumnModel().getColumn(1), 100);
            setColumnWidth(tabla.getColumnModel().getColumn(2), 250);
            setColumnWidth(tabla.getColumnModel().getColumn(3), 43);
            setColumnWidth(tabla.getColumnModel().getColumn(4), 72);
            ((DefaultTableCellRenderer)tabla.getTableHeader().getDefaultRenderer()).setHorizontalAlignment(JLabel.CENTER);
            for(int i=0;i<tabla.getColumnCount();i++){
                tabla.getColumnModel().getColumn(i).setCellRenderer(dtcr);
            }
        } catch (SQLException ex) {
            Logger.getLogger(TableUtils.class.getName()).log(Level.SEVERE, null, ex);
        }

    }

    public static void buscarAllGastos(JTable tabla, String nombre){
        Connection con = (Connection) MyConnection.getConnection();
        ListTableModel model = null;
        ResultSet rs;
        try {
            String query = "select * from gasto_general where nombre = '"+nombre+"'";

            Statement s = (Statement) con.createStatement();
            rs = s.executeQuery(query);
            model = ListTableModel.createModelFromResultSet( rs );
            tabla.setModel( model );


        } catch (SQLException ex) {
            Logger.getLogger(TableUtils.class.getName()).log(Level.SEVERE, null, ex);
        }
    }

    public static void fillAllPersonas(JTable tabla){
        Connection con = (Connection) MyConnection.getConnection();
        ListTableModel model = null;
        ResultSet rs;
        try {
            String query = "select nombre as Nombre, apellido as Apellido from persona";
            Statement s = (Statement) con.createStatement();
            rs = s.executeQuery(query);
            model = ListTableModel.createModelFromResultSet( rs );
            tabla.setModel( model );
        } catch (SQLException ex) {
            Logger.getLogger(TableUtils.class.getName()).log(Level.SEVERE, null, ex);
        }
    }

    public static void fillAllPersonasId(JTable tabla){
        Connection con = (Connection) MyConnection.getConnection();
        ListTableModel model = null;
        ResultSet rs;
        try {
            String query = "select id_persona as Id, nombre as Nombre, apellido as Apellido from persona";
            Statement s = (Statement) con.createStatement();
            rs = s.executeQuery(query);
            model = ListTableModel.createModelFromResultSet( rs );
            tabla.setModel( model );
            tabla.getColumnModel().getColumn(0).setPreferredWidth(1);
        } catch (SQLException ex) {
            Logger.getLogger(TableUtils.class.getName()).log(Level.SEVERE, null, ex);
        }
    }

    public static void searchGastosByDetalle(String busqueda, JTable tabla){
        Connection con = (Connection) MyConnection.getConnection();
        ListTableModel model = null;
        ResultSet rs;
        try {
            String query = "SELECT g.id_gasto as Id, CAST(CONCAT(p.nombre,' ',p.apellido) AS CHAR) AS Nombre, g.Denominacion AS Detalle, g.Monto AS Monto,"+
                           " g.Fecha AS Fecha, tg.descripcion AS Tipo"+
                           " FROM persona p"+
                           " JOIN gastos g ON p.id_persona = g.id_persona"+
                           " JOIN tipo_gasto tg ON g.id_tipo_gasto = tg.id_tipo_gasto"+
                           " WHERE g.denominacion LIKE '"+busqueda+"%'"+
                           " ORDER BY g.Fecha DESC";

            Statement s = (Statement) con.createStatement();
            rs = s.executeQuery(query);
            model = ListTableModel.createModelFromResultSet( rs );
            tabla.setModel( model );

            //se crea un nuevo renderer para centrar el texto
            DefaultTableCellRenderer dtcr = new DefaultTableCellRenderer();
            dtcr.setHorizontalAlignment(SwingConstants.CENTER);

            tabla.setModel( model );

            //invoca el metodo setColumnWidth, que permite redimensionar las columnas, usando maxwidth y preferredwidth
            setColumnWidth(tabla.getColumnModel().getColumn(0), 25);
            setColumnWidth(tabla.getColumnModel().getColumn(1), 100);
            setColumnWidth(tabla.getColumnModel().getColumn(2), 250);
            setColumnWidth(tabla.getColumnModel().getColumn(3), 43);
            setColumnWidth(tabla.getColumnModel().getColumn(4), 72);

            //se centra el texto del encabezado de la tabla
            ((DefaultTableCellRenderer)tabla.getTableHeader().getDefaultRenderer()).setHorizontalAlignment(JLabel.CENTER);

            //se setea el renderer para cada columna, de acuerdo a lo definido anteriormente. En este caso, para centrar el texto
            for(int i=0;i<tabla.getColumnCount();i++){
                tabla.getColumnModel().getColumn(i).setCellRenderer(dtcr);
            }
        } catch (SQLException ex) {
            Logger.getLogger(TableUtils.class.getName()).log(Level.SEVERE, null, ex);
        }
    }
}
